﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace pttk_htt_hr.Utility
{
    class SqlParameterUitl
    {
        public static List<List<SqlParameter>> MakeList_SqlParameter(List<Dictionary<string, string>> DataSource, string[] ArrParameters)
        {
            List<List<SqlParameter>> parameters = new List<List<SqlParameter>>();
            List<SqlParameter> parameter = null;

            for (int i = 0; i < DataSource.Count; i++)
            {
                Dictionary<string, string> DataRow = DataSource.ElementAt(i);
                parameter = new List<SqlParameter>();

                for (int j = 0; j < ArrParameters.Length; j++)
                {
                    if (DataRow.ContainsKey(ArrParameters[j]))
                    {
                        parameter.Add(new SqlParameter("@" + ArrParameters[j], DataRow[ArrParameters[j]]));
                    }
                }

                parameters.Add(parameter);
            }


            return parameters;
        }

        public static List<List<SqlParameter>> Add_SqlParameter_To_ListSqlParameter(List<List<SqlParameter>> LstSqlParameter, string parameterName, string parameterValue)
        {
            for (int i = 0; i < LstSqlParameter.Count; i++)
            {
                LstSqlParameter.ElementAt(i).Add(new SqlParameter("@"+parameterName, parameterValue));
            }

            return LstSqlParameter;
        }

        public static List<List<SqlParameter>> Update_SqlParameter_For_ListSqlParameter(List<List<SqlParameter>> LstSqlParameter, string parameterName, string parameterValue)
        {
            List<List<SqlParameter>> res = new List<List<SqlParameter>>();

            for (int i = 0; i < LstSqlParameter.Count; i++)
            {
                List<SqlParameter> x = LstSqlParameter.ElementAt(i);
                for (int j = 0; j < x.Count; j++)
                {
                    if (x[j].ParameterName.Equals(parameterName))
                    {
                        // update value
                        x[j].Value = parameterValue;
                        break;
                    }
                }
                
                res.Add(x);
            }

            return LstSqlParameter;
        }

        /*
        public static List<List<SqlParameter>> Remove_SqlParameter_Unnecessary(List<List<SqlParameter>> LstSqlParameter, string[] ArrNecessaryParameters)
        {
            List<List<SqlParameter>> Parameters = new List<List<SqlParameter>>();

            for (int i = 0; i < LstSqlParameter.Count; i++)
            {
                List<SqlParameter> DataRow = LstSqlParameter.ElementAt(i);

                for (int paramI = 0; paramI < DataRow.Count; paramI++)
                {
                    SqlParameter x = DataRow.ElementAt(paramI);
                    
                    bool hasExisted = false;
                    for (int necessaryParamI = 0; necessaryParamI < ArrNecessaryParameters.Length; necessaryParamI++)
                    {
                        if (x.SourceColumn.Equals(ArrNecessaryParameters[necessaryParamI]))
                        {
                            hasExisted = true;
                            break;
                        }
                    }

                    if (hasExisted == false)
                    {
                        DataRow.RemoveAt(paramI);
                    }

                    Parameters.Add(DataRow);
                }
                
            }


            return Parameters;
        }*/

        /// <summary>
        /// 
        /// </summary>
        /// <param name="LstSqlParameter"></param>
        /// <param name="ArrNecessaryParameters"></param>
        /// <returns></returns>
        public static List<SqlParameter> Remove_SqlParameter_Unnecessary(List<SqlParameter> LstSqlParameter, string[] ArrNecessaryParameters)
        {
            List<SqlParameter> ResultParameters = new List<SqlParameter>();
            List<SqlParameter> SourceParameters = LstSqlParameter;


            for (int paramI = 0; paramI < SourceParameters.Count; paramI++)
            {
                SqlParameter x = SourceParameters.ElementAt(paramI);

                bool hasExisted = false;
                for (int necessaryParamI = 0; necessaryParamI < ArrNecessaryParameters.Length; necessaryParamI++)
                {
                    if (x.ParameterName.Equals(ArrNecessaryParameters[necessaryParamI]))
                    {
                        hasExisted = true;
                        break;
                    }
                }

                if (hasExisted)
                {
                    ResultParameters.Add(x);
                }

            }

            return ResultParameters;
        }

        /// <summary>
        /// Mục đích: Lấy ra Param value trong List<SqlParameter> từ ParameterName cho trước
        /// Mô tả: 
        /// </summary>
        /// <param name="LstSqlparam">List phải có @</param>
        /// <param name="ParameterName">Bắt buộc có @</param>
        /// <returns></returns>
        public static string Find_ParameterName_In_ListSqlparameter(List<SqlParameter> LstSqlparam, string ParameterName)
        {
            string ParamVal = "";

            foreach (var item in LstSqlparam)
            {
                if (item.ParameterName.Equals(ParameterName))
                {
                    ParamVal = item.Value.ToString();
                    break;
                }
            }

            return ParamVal;
        }

        public static List<SqlParameter> Convert_Dictionary_To_ListSqlparameter(Dictionary<string, string> Dic)
        {
            List<SqlParameter> res = new List<SqlParameter>();

            // Lấy ra tập key trong dictionary
            string[] keys = Dic.Keys.ToArray();
            foreach (var item in keys)
            {
                string ParameterName = "@"+item; // item là key
                string Value = Dic[item]; // lấy value từ key

                res.Add(new SqlParameter(ParameterName, Value));
            }

            return res;
        }
    }
}
